#!/usr/bin/python
# -*- coding: utf-8 -*-

import MySQLdb
from db_utils import DBUtils 
from model.department import Department
from model.employee import Employee 
import utils.log_utils as log 

class EmployeeDB:

    def __init__(self):
        self.dbutils = DBUtils() 

    ##根据名字或POPO查询员工信息, 无参数则查询所有员工信息
    def query(self, name=None):
        emplist, params = [], () 
        sql = """
                select emp.name as emp_name, gender, popo, emp.dept_no, dept_name, 
                    tb_manager.name as manager_name 
                from 
                    employee emp, department dept,
                    (select name, dm.dept_no from employee emp right join dept_manager dm  
                    on popo=manager_popo) as tb_manager
                where 
                    emp.dept_no=tb_manager.dept_no and emp.dept_no=dept.dept_no
            """
        if name:
            name = MySQLdb.escape_string(name)
            sql += " and (emp.name like %s or popo like %s) ;"
            params = ('%%' + name + '%%', '%%' + name + '%%')
        rows = self.dbutils.execute(sql, params) 
        for row in rows:
            name, gender, popo = row[0], u'男' if row[1]=='M' else u'女', row[2]
            dept_no, dept_name, manager_name = row[3], row[4], row[5] or u'待定' 
            dept = Department(dept_no, dept_name, manager_name)
            emp = Employee(name, gender, popo, dept)
            emplist.append(emp)
        return emplist

    def query_cond(self, args):
        emplist, params = [], () 
        sql = """
                SELECT emp.name as emp_name, gender, popo, emp.dept_no, dept_name, 
                    tb_manager.name as manager_name 
                FROM 
                    employee emp, department dept,
                    (select name, dm.dept_no from employee emp right join dept_manager dm  
                    on popo=manager_popo) as tb_manager
                WHERE 
                    emp.dept_no=tb_manager.dept_no and emp.dept_no=dept.dept_no
            """
        name, gender, deptid = args.get('name'), args.get('gender'), args.get('deptid')
        if name and name != '':
            name = MySQLdb.escape_string(name)
            sql += " and (emp.name like %s or popo like %s) " 
            params = ('%%' + name + '%%', '%%' + name + '%%')
        if gender and gender != '':
            gender = MySQLdb.escape_string(gender)
            sql += " and gender='%s' "  % gender 
        if deptid and deptid != '':
            deptid = MySQLdb.escape_string(str(deptid))
            sql += " and emp.dept_no = '%s' " % deptid  
        rows = self.dbutils.execute(sql, params)
        #log.debug('args=%s, %s, %s' % (name, gender, deptid) )
        #log.debug('cond query: len=%d' % len(rows))
        for row in rows:
            name, gender, popo = row[0], u'男' if row[1]=='M' else u'女', row[2]
            dept_no, dept_name, manager_name = row[3], row[4], row[5] or u'待定' 
            dept = Department(dept_no, dept_name, manager_name)
            emp = Employee(name, gender, popo, dept)
            emplist.append(emp)
        return emplist 


    ##查询部门统计信息
    def query_dept(self):
        deptlist = [] 
        sql = """
            SELECT dept_name, tb_manager.name as leader_name, count(*) as count 
            FROM employee emp, department dept, 
                 (select dm.dept_no, name from employee emp right join dept_manager dm  
                 on popo=manager_popo) as tb_manager 
            WHERE 
                 emp.dept_no=tb_manager.dept_no and dept.dept_no=emp.dept_no 
                 group by tb_manager.dept_no;
            """
        rows = self.dbutils.execute(sql)
        for row in rows:
            dept_name, manager_name, dept_no = row[0], row[1] or u'待定', row[2] 
            dept = Department(dept_no, dept_name, manager_name)
            deptlist.append(dept)
        return deptlist 


